/**
 * UserDaoImpl.java
 * Created at 2017-07-06
 * Created by Jackie liu
 * Copyright (C) 2014, All rights reserved.
 */
package com.huabo.dao.impl.oracle;

import com.huabo.base.BaseDaoImpl;
import com.huabo.dao.inter.UserDao;
import com.huabo.model.Organization;
import com.huabo.model.Position;
import com.huabo.model.RoleUser;
import com.huabo.model.User;
import com.huabo.sql.builder.SelectBuilder;
import com.huabo.sql.builder.WhereBuilder;
import com.huabo.utils.Page;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * <p>ClassName: UserDaoImpl</p>
 * <p>Description: 用户管理Dao实现</p>
 * <p>Author: Jackie liu</p>
 * <p>Date: 2017-07-06</p>
 */
@Repository
public class UserDaoImpl extends BaseDaoImpl<User, Long> implements UserDao {

    @Override
    public List<User> queryByAccount(String account) {
        WhereBuilder wc = new WhereBuilder();
        wc.andEquals("F_POLICE_NO", account);
        return query(wc);
    }

    @Override
    public List<User> queryByRoleId(Long roleId) {
        //String sql = String.format("SELECT U.%s FROM %s U INNER JOIN TR_ROLE_USER RU ON U.F_ID = RU.F_USER_ID WHERE F_ROLE_ID = ?", joinColumn(", U."), domainModelAnalysis.getTableName());
        WhereBuilder wb = new WhereBuilder();
        wb.andEquals("RU.F_USER_ID", roleId);

        SelectBuilder selectBuilder = selectBuilder("U");
        selectBuilder.from(domainModelAnalysis.getTableName()).alias("U").build()
                .join(RoleUser.class).alias("RU").on("U.F_ID = RU.F_USER_ID").build();

        return query(selectBuilder.getSql(), wb);
    }

    @Override
    public boolean isDuplicateName(Long id, Long orgId, String name) {
        WhereBuilder wb = new WhereBuilder();
        wb.andEquals("F_ORG_ID", orgId);
        wb.andEquals("F_NAME", name);
        wb.andNotEquals("F_ID", id);
        return count(wb) > 0;
    }

    @Override
    public boolean isDuplicatePoliceNo(Long id, String policeNo) {
        return isDuplicateField(id, policeNo, "_POLICE_NO");
    }

    @Override
    public boolean isDuplicateIdCard(Long id, String idCard) {
        return isDuplicateField(id, idCard, "F_IDCARD");
    }

    @Override
    public Page<User> queryPage(Map<String, Object> params) {
        WhereBuilder queryCondition = new WhereBuilder();
        queryCondition.setOrderBy("F_POLICE_NO");
        if (params != null) {
            initPageParam(queryCondition, params);
            queryCondition.andFullLike("F_ORG_ID", params.get("orgId"));
            String searchInput = (String) params.get("searchInput");
            if (StringUtils.isNotEmpty(searchInput)) {
                queryCondition.andGroup()
                        .andFullLike("F_NAME", searchInput)
                        .orFullLike("F_POLICE_NO", searchInput);
            }
        }

        /*String sql = "SELECT A.*,B.F_NAME AS F_ORG_NAME,C.F_NAME AS F_POSITION_NAME FROM"
                + " TS_USER A LEFT JOIN TS_ORGANIZATION B ON A.F_ORG_ID=B.F_ID"
                + " LEFT JOIN TS_POSITION C ON A.F_POSITION_ID=C.F_ID";*/

        SelectBuilder selectBuilder = selectBuilder("A");
        selectBuilder.column("B.F_NAME AS F_ORG_NAME")
                .column("C.F_NAME AS F_POSITION_NAME")
                .from(domainModelAnalysis.getTableName()).alias("A").build()
                .leftJoin(Organization.class).alias("B").on("A.F_ORG_ID = B.F_ID").build()
                .leftJoin(Position.class).alias("C").on("A.F_POSITION_ID = C.F_ID").build();

        return queryPage(selectBuilder.getSql(), queryCondition);
    }

    @Override
    public User queryById(Long id) {
        /*String sql = "SELECT A.*,B.F_NAME AS F_ORG_NAME,C.F_NAME AS F_POSITION_NAME FROM"
                + " TS_USER A LEFT JOIN TS_ORGANIZATION B ON A.F_ORG_ID=B.F_ID"
                + " LEFT JOIN TS_POSITION C ON A.F_POSITION_ID=C.F_ID  WHERE A.F_ID = ?";*/

        WhereBuilder wb = new WhereBuilder();
        wb.andEquals("A.F_ID", id);

        SelectBuilder selectBuilder = selectBuilder("A");
        selectBuilder.column("B.F_NAME AS F_ORG_NAME")
                .column("C.F_NAME AS F_POSITION_NAME")
                .from(domainModelAnalysis.getTableName()).alias("A").build()
                .leftJoin(Organization.class).alias("B").on("A.F_ORG_ID = B.F_ID").build()
                .leftJoin(Position.class).alias("C").on("A.F_POSITION_ID = C.F_ID").build();

        List<User> list = query(selectBuilder.getSql(), wb);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public int modifyState(final Long[] ids, final int state) {
        String sql = String.format("UPDATE %s SET F_STATE = ? WHERE F_ID = ?", domainModelAnalysis.getTableName());
        int[] batchUpdate = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, state);
                ps.setLong(2, ids[i]);
            }

            @Override
            public int getBatchSize() {
                return ids.length;
            }
        });
        return batchUpdate.length;
    }

    @Override
    public List<User> queryByOrgId(Long orgId) {
        WhereBuilder wb = new WhereBuilder();
        wb.andCustomSQL("F_ORG_ID IN (SELECT F_ID FROM TS_ORGANIZATION WHERE F_ID_PATH LIKE ?)", new Object[]{"%" + orgId + "%"});
        return query(wb);
    }

    @Override
    public int modifyPass(final Long[] ids, final String pass) {
        String sql = String.format("UPDATE %s SET F_PASS = ? WHERE F_ID = ?", domainModelAnalysis.getTableName());
        int result = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setString(1, pass);
                preparedStatement.setLong(2, ids[i]);
            }

            @Override
            public int getBatchSize() {
                return ids.length;
            }
        }).length;
        return result;
    }
}